#!/use/bin/python
# coding=utf-8
# 早间开机 数据分析-早间开机-服务推送成功率
#LOG_DIVICE_NEW_ACTIVE_V3_ZJKJ_*  每天早间咨询弹窗转化 新增订阅的设备激活id LOG_DIVICE_NEW_SHOW_ZJKJ_* 每天早间播报的激活id
import redis
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

# 记录 数据分析-早间开机-服务推送成功率 analysis_zjkj_push_rate
def insertPushRate(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjkj_push_rate 表是否已经存在当天 数据
        selectCountSql = """
                    select count(1) num from analysis_zjkj_push_rate t where 1=1 and t.ref_date =  '%s'
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日早间开机弹窗数(只要成功弹窗就算)
        selectAlertNumSql = """
                    select count(DISTINCT(t.active_id)) from log_zjkj_yl t where t.result = 1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                """ % date;
        cur.execute(selectAlertNumSql)
        resultAlertNum = cur.fetchone()
        # 获取当日早间开机弹窗数(用户看到)
        selectNumSql = """
                    select count(DISTINCT(t.active_id)) from log_zjkj_yl t where t.result = 1 and t.see = 1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                """ % date;
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        # 获取所有早间开机弹窗数(用户看到)
        selectAllNumSql = """
                           select count(1) from log_zjkj_yl t where t.see = 1 
                       """;
        cur.execute(selectAllNumSql)
        resultAllNum = cur.fetchone()
        #计算 收到服务弹窗推送并在当天成功开启服务的用户量
        selectOpenNumSql = """
                    select count(DISTINCT(o.active_id)) from log_common_openser o join log_zjkj_yl t on o.active_id = t.active_id 
        and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = DATE_FORMAT(o.create_time, '%%Y-%%m-%%d')
        and DATE_FORMAT(o.create_time, '%%Y-%%m-%%d') = '%s' and t.see = 1  and serId ='ZJKJ'
                """ % date
        cur.execute(selectOpenNumSql)
        resultOpenNum = cur.fetchone()
        #将当日 新增的订阅激活id存入redis
        selectOpenActiveSql = """
                           select DISTINCT(o.active_id) from log_common_openser o join log_zjkj_yl t on o.active_id = t.active_id 
               and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = DATE_FORMAT(o.create_time, '%%Y-%%m-%%d')
               and DATE_FORMAT(o.create_time, '%%Y-%%m-%%d') = '%s' and t.see = 1  and serId ='ZJKJ'
                       """ % date
        cur.execute(selectOpenActiveSql)
        resultOpenActive = cur.fetchall()
        r = getReidsConnection()
        key = "LOG_DIVICE_NEW_ACTIVE_V3_ZJKJ_" + date.replace('-','')
        if resultOpenActive != None:
            for result in resultOpenActive:
                r.sadd(key, result[0])
        print('向%s key中增加了%s条数据' % (key, r.scard(key)))
        # 计算早间开机推送成功率
        pushRate = 0
        alertNum = resultAlertNum[0]
        newPushNum = resultNum[0]
        cumulatePushNum = resultAllNum[0]
        openNum = resultOpenNum[0]
        if newPushNum!=0:
            if openNum > newPushNum:
                pushRate = 100
            else:
                pushRate = round(openNum/newPushNum,4)*100

        if countNum == 0:
            logDetail = "%s日新增-早间开机弹窗推送数量为%s,用户看到的量为%s,累计推送数为%s,当天成功开启服务的设备量为%s,成功率为%s" % (date,alertNum,newPushNum,cumulatePushNum,openNum,pushRate)
            print(logDetail)
            # 插入早间服务弹窗转化率
            insertSql = """insert into analysis_zjkj_push_rate(alert_num,new_num,cumulate_num,open_num,success_rate,ref_date) 
                value(%s,%s,%s,%s,%s,'%s')""" % (alertNum,newPushNum,cumulatePushNum,openNum,pushRate,date)
            cur.execute(insertSql)
        else:
            logDetail = "%s日更新-早间开机弹窗推送数量为%s,用户看到的量为%s,累计推送数为%s,当天成功开启服务的设备量为%s,成功率为%s" % (date,alertNum,newPushNum,cumulatePushNum,openNum,pushRate)
            print(logDetail)
            # 更新早间服务弹窗转化率
            updateSql = """
                update analysis_zjkj_push_rate set alert_num = %s ,new_num = %s ,cumulate_num = %s,open_num = %s ,success_rate = %s ,update_time = now()  where
                    ref_date = '%s' 
            """ % (alertNum,newPushNum,cumulatePushNum,openNum,pushRate,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#记录当日弹窗新增的订阅设备 第二天进行早间播报的数量
def insertNextdayShowNum(date,beforeDate):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjkj_push_rate 表是否已经存在当天 数据
        selectCountSql = """
                    select count(1) num from analysis_zjkj_push_rate t where 1=1 and t.ref_date =  '%s'
                """ % beforeDate
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取当日早间开机 早上启动播报数量
        showNumSql = """
                            select DISTINCT(t.active_id) from log_zjkj_page_show t where 1=1 and t.result =1 and DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s'
                        """ % date;
        cur.execute(showNumSql)
        showNumResult = cur.fetchall()
        r = getReidsConnection()
        newActiveKey = "LOG_DIVICE_NEW_ACTIVE_V3_ZJKJ_" + beforeDate.replace('-', '')
        newShowKey = "LOG_DIVICE_NEW_SHOW_ZJKJ_" + date.replace('-', '')
        # 将每天早间开机启动的设备激活id 存入redis
        if showNumResult != None:
            for result in showNumResult:
                r.sadd(newShowKey, result[0])
        print('向%s key中增加了%s条数据' % (newShowKey, r.scard(newShowKey)))
        # 当天因咨询弹窗转化的新订阅设备,第二天早间开机的数量
        nextdayShowNum = len(r.sinter(newShowKey, newActiveKey))
        nextdayShowRate = 0
        newActiveNum = r.scard(newActiveKey)
        if newActiveNum!=0:
            if nextdayShowNum > newActiveNum:
                nextdayShowRate = 100
            else:
                nextdayShowRate = round(nextdayShowNum/newActiveNum,4)*100
        if countNum != 0:
            logDetail = "%s日更新-当天因咨询弹窗转化的新订阅设备为%s,且第二天早间开机的数量为%s,比例为%s" % (date,newActiveNum,nextdayShowNum,nextdayShowRate)
            print(logDetail)
            updateSql = """
                            update analysis_zjkj_push_rate set nextday_show_num = %s ,show_rate = %s ,update_time = now()  where
                                ref_date = '%s' 
                        """ % (nextdayShowNum, nextdayShowRate, beforeDate);
            cur.execute(updateSql)
        saveLog(logDetail, beforeDate)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()


# 记录 数据分析-早间开机-服务推送成功率 analysis_zjkj_push_rate --月度
def insertPushRateMonth(toDay,toMonthDay):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_zjkj_push_rate 表是否已经存在当天 数据
        selectCountSql = """
                    select count(1) num from analysis_zjkj_push_rate t where 1=1 and t.ref_date =  '%s'
                """ % toMonthDay
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        # 获取本月早间开机弹窗数(只要成功弹窗就算)
        selectAlertNumSql = """
                                   select sum(alert_num) from analysis_zjkj_push_rate t where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' 
                               """ % (toMonthDay, toMonthDay);
        cur.execute(selectAlertNumSql)
        resultAlertNum = cur.fetchone()
        # 获取当日所有早间开机弹窗数(用户看到) 作为当
        # 获取本月早间开机弹窗数(用户看到)
        selectNumSql = """
                           select sum(new_num) from analysis_zjkj_push_rate t where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' 
                       """ % (toMonthDay,toMonthDay);
        cur.execute(selectNumSql)
        resultNum = cur.fetchone()
        # 获取当日所有早间开机弹窗数(用户看到) 作为当月所有早间开机弹窗数
        selectAllNumSql = """
                           select cumulate_num from analysis_zjkj_push_rate t where  t.ref_date = '%s' 
                       """ % (toDay);
        cur.execute(selectAllNumSql)
        resultAllNum = cur.fetchone()
        #计算 收到服务弹窗推送并在当天成功开启服务的用户量 --月度累计
        selectOpenNumSql = """
                           select sum(open_num) from analysis_zjkj_push_rate t where SUBSTR( t.ref_date, 1,7) = '%s' and t.ref_date != '%s' 
                       """ % (toMonthDay,toMonthDay);
        cur.execute(selectOpenNumSql)
        resultOpenNum = cur.fetchone()
        # 计算早间开机推送成功率
        pushRate = 0
        alertNum = resultAlertNum[0]
        openNum = resultOpenNum[0]
        newPushNum = resultNum[0]
        cumulatePushNum = resultAllNum[0]
        if newPushNum!=0:
            if openNum > newPushNum:
                pushRate = 100
            else:
                pushRate = round(openNum/newPushNum,4)*100

        if countNum == 0:
            logDetail = "%s月份新增-早间开机弹窗推送数量为%s,用户看到的量为%s,累计推送数为%s,当天成功开启服务的设备量为%s,成功率为%s" % (toMonthDay,alertNum,newPushNum,cumulatePushNum,openNum,pushRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_zjkj_push_rate(alert_num,new_num,cumulate_num,open_num,success_rate,ref_date) 
                value(%s,%s,%s,%s,%s,'%s')""" % (alertNum,newPushNum,cumulatePushNum,openNum,pushRate,toMonthDay)
            cur.execute(insertSql)
        else:
            logDetail = "%s月份更新-早间开机弹窗推送数量为%s,用户看到的量为%s,累计推送数为%s,当天成功开启服务的设备量为%s,成功率为%s" % (toMonthDay,alertNum,newPushNum,cumulatePushNum,openNum,pushRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_zjkj_push_rate set alert_num = %s , new_num = %s ,cumulate_num = %s,open_num = %s ,success_rate = %s ,update_time = now()  where
                    ref_date = '%s' 
            """ % (alertNum,newPushNum,cumulatePushNum,openNum,pushRate,toMonthDay);
            cur.execute(updateSql)
        saveLog(logDetail, toMonthDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getMonthdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m')
    return re_date

def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 29
        jobName = '核心指标-早间开机-推送成功率'
        tableName = 'analysis_zjkj_push_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    beforeDate = getdate(2)
    monthdate = getMonthdate(1)
    #today = "2021-12-13"
    print ("%s 日期,更新<数据分析-早间开机-服务推送成功率>报表" % date)
    insertPushRate(date)
    insertPushRateMonth(date, monthdate)
    insertNextdayShowNum(date,beforeDate)

